package com.pactera.asmp.server.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.pactera.asmp.server.common.constants.ResultCode;

public class ExcelUtils {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    // 2003- 版本的excel
    private static final String SUFFIX_XLS = "xls";
    // 2007+ 版本的excel
    private static final String SUFFIX_XLSX = "xlsx";

    /**
     * 读取Excel
     * 
     * @param filePath
     * @param fields
     * @return
     * @throws IOException
     */
    public static List<List<String>> read(String fileType, MultipartFile file) {
        List<List<String>> resultList = Lists.newArrayList();
        try {
            Workbook wb = generateWorkBook(fileType, file.getInputStream());
            Sheet sheetAt = wb.getSheetAt(0);
            FormulaEvaluator evaluator = sheetAt.getWorkbook().getCreationHelper().createFormulaEvaluator();
            int lastRowNum = sheetAt.getLastRowNum();
            if (lastRowNum == 0) {
                return null;
            }
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheetAt.getRow(i);
                int lastCellNum = row.getLastCellNum();
                List<String> data = Lists.newArrayList();
                for (int j = 0; j < lastCellNum; j++) {
                    if(null == row.getCell(j)) {
                        data.add(null);
                    }else {
                        Object cell= getCellValue(row.getCell(j), evaluator);
                        data.add(null == cell ? null : String.valueOf(cell));
                    }
                }
                resultList.add(data);
            }
            return resultList;
        } catch (Exception ex) {
            logger.error("read csv error, {}", ex.getMessage());
            return null;
        }
    }

    /**
     * 生成workbook
     * 
     * @param fileType
     * @param inputStream
     * @return
     * @throws Exception
     */
    private static Workbook generateWorkBook(String fileType, InputStream inputStream) throws Exception {
        Workbook wb;
        if (SUFFIX_XLS.equals(fileType)) {
            // 2003-
            wb = new HSSFWorkbook(inputStream);
        } else if (SUFFIX_XLSX.equals(fileType)) {
            // 2007+
            wb = new XSSFWorkbook(inputStream);
        } else {
            throw new Exception(ResultCode.FILE_ACCEPT_NOT_SUPPORT.message());
        }
        return wb;
    }

    /**
     * 获取cell值
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell, FormulaEvaluator evaluator) {
        Object value = "";
        switch (cell.getCellType()) {
            case FORMULA:  //公式类型
                // 先计算表达式 
                value = String.valueOf(evaluator.evaluate(cell).getNumberValue());
                break;
           case BOOLEAN:  //布尔类型
                value = String.valueOf(cell.getBooleanCellValue()); 
                break; 
           case STRING:   // 字符串类型
                value = cell.getStringCellValue();
                break;
           case NUMERIC:  // 数值类型
                // 日期格式
                if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    value =   DateUtil.format(cell.getDateCellValue());
                }else {
                   // 四舍五入
                    value = new DecimalFormat("#.####").format(cell.getNumericCellValue());
                }
                break;
           case BLANK:
                value = null;
                break;
           default: //其它类型
                break;
        }
        return value;
    }

    /**
     * 写入excel数据
     *
     * @param response
     * @param headerList
     * @param dataList
     * @param fileName
     */
    public static void writeExcel(HttpServletResponse response, List<String> headerList, List<List<String>> dataList, String fileName) {
        Workbook workbook = new XSSFWorkbook();
        // 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        Sheet sheet = workbook.createSheet("sheet1");
        // 新建文件
        OutputStream out = null;
        try {
            fileName = fileName + "." + SUFFIX_XLSX;
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // 否则，直接写到输出流中
            out = response.getOutputStream();

            // 写入excel的表头
            Row row = sheet.createRow(0);

            for (int i = 0; i < headerList.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(headerList.get(i));
            }

            if(CollectionUtils.isNotEmpty(dataList) && CollectionUtils.isNotEmpty(dataList.get(0))) {

                for (int i = 0; i < dataList.size(); i++) {
                    row = workbook.getSheet("sheet1").createRow(i + 1);
                    for (int j = 0; j < dataList.get(i).size(); j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellValue(dataList.get(i).get(j));
                    }
                }

            }
            workbook.write(out);
        } catch (Exception e) {
            logger.debug("导出写Excel异常, {}", e.getMessage());
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    logger.debug("导出写Excel异常");
                }
            }
        }
    }
}
